package com.lovo.school.dao.impl;

import com.lovo.common.dao.BaseDao;
import com.lovo.common.model.CutPageBean;
import com.lovo.common.model.EmpBean;
import com.lovo.common.model.SchoolBean;
import com.lovo.school.dao.ISchoolDao;

import java.sql.SQLException;

/**
 * 学校管理的Dao实现类
 * @author 白德柯
 *
 */
public class SchoolDaoImpl extends BaseDao implements ISchoolDao {
    @Override
    public CutPageBean<SchoolBean> findByItem(int page, SchoolBean schoolBean) {
        String sql = "SELECT sc.*,sys.`dict_value` status_value FROM school sc \n" +
                "JOIN sys_dict sys ON sc.`status`=sys.`id` \n" +
                "WHERE sc.`city_id`=? ";
        String conditionSql = "";
        if(schoolBean.getName() != null && !schoolBean.getName().equals("")){
            conditionSql += "AND sc.`school_name` LIKE '%"+schoolBean.getName()+"%' ";
        }
        if(schoolBean.getStatus() != 3){
            conditionSql += "AND sc.`status`= " + schoolBean.getStatus();
        }

        CutPageBean<SchoolBean> cutPageBean = new CutPageBean<>();
        this.setConnection();
        try {
            ps = con.prepareStatement(sql + conditionSql + CutPageBean.getPageSql(page));
            ps.setInt(1,schoolBean.getCityId());
            System.out.println(sql + conditionSql + CutPageBean.getPageSql(page));
            rs = ps.executeQuery();
            while(rs.next()){
                cutPageBean.getList().add(fill());
            }
            ps = con.prepareStatement("SELECT COUNT(*) cnt FROM ("+ sql + conditionSql +") t");
            ps.setInt(1,schoolBean.getCityId());
            rs = ps.executeQuery();
            rs.next();
            // 获取员工总数并设置给分页对象
            cutPageBean.setCount(rs.getInt("cnt"));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.close();
        }
        return cutPageBean;
    }

    private SchoolBean fill() throws SQLException {
        SchoolBean schoolBean = new SchoolBean();
        schoolBean.setId(rs.getInt("id"));
        schoolBean.setDeptId(rs.getInt("dept_id"));

        if(isColumnExist("dept_name")){
            schoolBean.setDeptName(rs.getString("dept_name"));
        }

        schoolBean.setEmpId(rs.getInt("emp_id"));
        schoolBean.setCityId(rs.getInt("city_id"));
        schoolBean.setName(rs.getString("school_name"));
        schoolBean.setLeader(rs.getString("leader"));
        schoolBean.setAddr(rs.getString("addr"));
        schoolBean.setStudentCount(rs.getInt("student_count"));
        schoolBean.setTeacherCount(rs.getInt("teacher_count"));
        schoolBean.setPhone(rs.getString("phone"));
        schoolBean.setInfo(rs.getString("info"));
        schoolBean.setIp(rs.getString("ip"));
        schoolBean.setStatus(rs.getInt("status"));

        if(isColumnExist("status_value")){
            schoolBean.setStatusValue(rs.getString("status_value"));
        }
        if(rs.getDate("create_date") != null){
            schoolBean.setCreateDate(rs.getDate("create_date").toLocalDate());
        }
        if(rs.getDate("apply_for_date") != null){
            schoolBean.setApplyForDate(rs.getDate("apply_for_date").toLocalDate());
        }
        if(rs.getDate("pass_date") != null){
            schoolBean.setPassDate(rs.getDate("pass_date").toLocalDate());
        }
        schoolBean.setApprovalOpinion(rs.getString("approval_opinion"));
        return schoolBean;
    }

    public static void main(String[] args) {
        SchoolDaoImpl schoolDao = new SchoolDaoImpl();
        SchoolBean schoolBean = new SchoolBean();
        schoolBean.setCityId(10);
        schoolBean.setStatus(4);
        System.out.println(schoolDao.findByItem(1,schoolBean));
    }
}
